﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using Common;

using System.IO;
using System.Data.OleDb;

namespace DAL
{
    public class WxStock
    {
        public Dat_WxStock SelectWxStock(int WxStockKey)
        {
            CIMDataContext db = new CIMDataContext();

            try
            {
                return db.Dat_WxStock.Single(w => w.WxStockKey == WxStockKey);
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        //添加
        public bool AddWxStock(Dat_WxStock wxstock)
        {
            bool Success;
            CIMDataContext db = new CIMDataContext();

            db.Dat_WxStock.InsertOnSubmit(wxstock);
            try
            {
                db.SubmitChanges();
                Success = true;
            }
            catch (Exception ex)
            {
                WebUtility.Print(ex.ToString());
                Success = false;
            }
            return Success;
        }

        public void DelWxStock(int WxStockKey)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_WxStock wxstock = db.Dat_WxStock.Single(w => w.WxStockKey == WxStockKey);
            db.Dat_WxStock.DeleteOnSubmit(wxstock);
            db.SubmitChanges();
        }

        //修改
        public bool EditWxStock(Dat_WxStock wxstock)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_WxStock WxStock = db.Dat_WxStock.Single(w => w.WxStockKey == wxstock.WxStockKey);

            WxStock.InDanNum = wxstock.InDanNum;
            WxStock.StockName = wxstock.StockName;
            WxStock.Spec = wxstock.Spec;
            WxStock.Mistake = wxstock.Mistake;
            WxStock.ReceiveTime = wxstock.ReceiveTime;
            WxStock.Price = wxstock.Price;
            WxStock.MUserKey = wxstock.MUserKey;
            WxStock.StockStatus = wxstock.StockStatus;
            WxStock.InUserKey = wxstock.InUserKey;

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool OutWxStock(Dat_WxStock wxstock)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_WxStock WxStock = db.Dat_WxStock.Single(w => w.WxStockKey == wxstock.WxStockKey);

            if (wxstock.WxStatus == "已修复")
            {
                WxStock.WxStatus = wxstock.WxStatus;
                WxStock.FinalPrice = wxstock.FinalPrice;
                WxStock.StockStatus = wxstock.StockStatus;
                WxStock.OutUserKey = wxstock.OutUserKey;
            }
            if (wxstock.WxStatus == "更换")
            {
                WxStock.WxStatus = wxstock.WxStatus;
                WxStock.ChangeStock = wxstock.ChangeStock;
                WxStock.SalePrice = wxstock.SalePrice;
                WxStock.StockStatus = wxstock.StockStatus;
                WxStock.OutUserKey = wxstock.OutUserKey;
            }
            if (wxstock.WxStatus == "更换")
            {
                WxStock.WxStatus = wxstock.WxStatus;
                WxStock.StockStatus = wxstock.StockStatus;
                WxStock.OutUserKey = wxstock.OutUserKey;
            }

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //条件查询
        public IQueryable<Dat_WxStock> SelectWxStock(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_WxStock> Query;

            if (QueryTable == null)
            {
                Query = from w in db.Dat_WxStock
                        orderby w.WxStockKey descending
                        select w;
            }
            else
            {
                Query = from w in GetConditionQuery(QueryTable)
                        orderby w.WxStockKey descending
                        select w;
            }
            return Query;
        }

        private IQueryable<Dat_WxStock> GetConditionQuery(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            var predicate = PredicateBuilder.True<Dat_WxStock>();

            if (QueryTable["StockStatus"] != null)
                predicate = predicate.And(w => w.StockStatus == QueryTable["StockStatus"].ToString());
            if (QueryTable["Keyword"] != null)
                predicate = predicate.And(i => 1 == 1);
            if (QueryTable["OneDate"] != null)
            {
                try
                {
                    DateTime OneDate = Convert.ToDateTime(QueryTable["OneDate"]);
                    predicate = predicate.And(w => w.ReceiveTime >= OneDate);
                }
                catch (Exception ex) { }
            }
            if (QueryTable["TwoDate"] != null)
            {
                try
                {
                    DateTime TwoDate = Convert.ToDateTime(QueryTable["TwoDate"].ToString().Split(' ')[0] + " 23:59:59");
                    predicate = predicate.And(w => w.ReceiveTime <= TwoDate);
                }
                catch (Exception ex) { }
            }

            return db.Dat_WxStock.Where(predicate);
        }

        //Export
        public static void DataToExcel(Hashtable QueryTable)
        {
            IQueryable<Dat_WxStock> Query = new WxStock().SelectWxStock(QueryTable);

            WebUtility.DelExport();

            string FileName = "~/export/" + Guid.NewGuid().ToString() + ".xls";
            string sNewFullFile = HttpContext.Current.Server.MapPath(FileName);

            try
            {
                File.Copy(HttpContext.Current.Server.MapPath("~/common/template.xls"), sNewFullFile);
            }
            catch (Exception er)
            {
                HttpContext.Current.Response.Write(er.Message);
                return;
            }

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbCommand cmd = null;

            bool bRet = false;

            try
            {
                conn.Open();
                cmd = new OleDbCommand("create table [报修物品信息]([入库预约序号] Text,[物品名称] Text,[规格描述] Text,[故障描述] Text,[报价价格] Text,[收取时间] Text,[经手人] Text)", conn);
                cmd.ExecuteNonQuery();


                string strSQL = "INSERT INTO [报修物品信息$] ([入库预约序号], [物品名称],[规格描述],[故障描述],[报价价格],[收取时间],[经手人]) VALUES (?,?,?,?,?,?,?)";
                cmd = new OleDbCommand(strSQL, conn);
                for (int i = 0; i < 7; i++)
                {
                    cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
                }
                foreach (Dat_WxStock wxstock in Query)
                {
                    cmd.Parameters[0].Value = string.IsNullOrEmpty(wxstock.InDanNum) ? "" : wxstock.InDanNum;
                    cmd.Parameters[1].Value = string.IsNullOrEmpty(wxstock.StockName) ? "" : wxstock.StockName;
                    cmd.Parameters[2].Value = string.IsNullOrEmpty(wxstock.Spec) ? "" : wxstock.Spec;
                    cmd.Parameters[3].Value = string.IsNullOrEmpty(wxstock.Mistake) ? "" : wxstock.Mistake;
                    cmd.Parameters[4].Value = wxstock.Price == null ? "" : WebUtility.DecimalToString(wxstock.Price);
                    cmd.Parameters[5].Value = wxstock.ReceiveTime == null ? "" : Convert.ToDateTime(wxstock.ReceiveTime).ToString("yyyy-MM-dd");
                    cmd.Parameters[6].Value = wxstock.MUserKey == null ? "" : new Account().GetAccountName(wxstock.MUserKey);

                    cmd.ExecuteNonQuery();
                }
                bRet = true;
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message);

            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                conn.Dispose();
            }

            if (bRet)
                HttpContext.Current.Response.Redirect(FileName);
        }
    }
}
